<?php
namespace app\admin\model;
use app\admin\model\OrderPhoneM;
use think\Db;
use think\Model;

/**
 * 订单模型
 * @package app\admin\model
 */
class OrdersM extends Model {
	// 设置当前模型对应的完整数据表名称
	protected $table = 'bus_order';

	//时间数组
	protected $timearr = [];
	//搜索条件
	protected $whereSql = [];

	/**
	 * 指定条件查询数据集
	 * @Author   lf
	 * @DateTime 2018-07-10T11:16:13+0800
	 * @param    [type]                   $request [条件]
	 * @return   [type]                            [返回数据集]
	 */
	public function getOrderAll($request) {
		//获取页数、每页行数
		$limit = $request['limit'];
		$page  = $request['page'];
		unset($request['limit']);
		unset($request['page']);
		//账号绑定的渠道或者通道
		$relate_id = session('relate_id');

		//时间处理根据时间起始返回每一天的日期[2020-01-12,2020-01-13,...]
		$timeFor = $this->getDateFromRange($request['st'], $request['et']);
		if (count($timeFor) <= 0) {
			return ['total' => 0, 'data' => 0];
		}

		$whereSql = $this->getMysqlWhere($request);

		//拼装分表UNION
		$countSql = $this->buildPartitionSqlCount('bus_order', 'ord.*', $timeFor, $whereSql);
		//每天的条数
		$total = $this->query("SELECT num , create_time FROM " . $countSql);

		//特殊处理数据cid:3551的24号
		if ($request['cha'] == 3551 && in_array('200924', $timeFor)) {
			foreach ($total as $key => $value) {
				if ($value['create_time'] == '2020-09-25') {
					$total[$key]['create_time'] = '2020-09-24';
				}
			}
		}
		//返回总数
		$list['total'] = 0;

		//查询的日期
		$dateArry = [];

		//查询记录
		$page = ($page - 1) * $limit;
		if ($page < 0) {
			$page = 0;
		}

		$isok = 0;

		//计算n页时limit从什么值开始
		$LimtList = [];

		foreach ($total as $key => $value) {
			if ($value['num'] == 0) {
				continue;
			}
			//前面的数总和
			$Limtone['num']         = $value['num'];
			$Limtone['oldnum']      = $list['total'];
			$Limtone['create_time'] = substr(str_replace('-', '', $value['create_time']), -6);
			//总数
			$list['total'] += $value['num'];
			//查询 $page 和 $page+($limit-1) 在哪个范围内
			//定位当前查询的记录是在哪个分表里
			if ((
				($page <= $list['total'] && ($page + $limit) > $list['total'])
				||
				($page <= $list['total'] && ($page + $limit) <= $list['total'])
			) && $isok == 0) {
				if (!in_array($value['create_time'], $dateArry)) {
					$dateArry[] = $Limtone['create_time'];
				}

				if ($page <= $list['total'] && ($page + $limit) <= $list['total']) {
					$isok = 1;
				}
			}

			$LimtList[$Limtone['create_time']] = $Limtone;
		}

		if ($list['total'] > 0) {
			$listSql = $this->buildPartitionSqlList('bus_order', 'ord.id,
                    ord.cid,
                    ord.plat_id,
                    ord.isp_host,
                    ord.channel_order_id,
                    ord.price,
                    ord.cost,
                    ord.sms_size,
                    ord.success_size,
                    ord.failure_size,
                    ord.success_phone_size,
                    ord.fee,
                    ord.phone_size,
                    ord.port_number,
                    ord.order_id,
                    ord.order_code,
                    ord.create_time', $dateArry, $whereSql, $LimtList);
			//拼装查询
			$qurySql = "SELECT sorder.*,cha.company_name,pla.platfrom FROM (SELECT * FROM " . $listSql . " ORDER BY create_time DESC LIMIT 0 , {$limit}) sorder";

			//连表
			$qurySql .= " LEFT JOIN bus_channel cha ON cha.channel_id = sorder.cid ";
			//连表
			$qurySql .= " LEFT JOIN bus_platfrom pla ON pla.plat_id = sorder.plat_id ";
			//查询
			$queryData = $this->query($qurySql);
		} else {
			$queryData = [];
		}

		//处理结果
		foreach ($queryData as $key => $value) {
			//处理渠道号和名称一起
			$queryData[$key]['company_name'] = $value['cid'] . '/' . $value['company_name'];

			//只有导出才有100行以上
			if ($limit < 100) {

				$create_time = substr(str_replace("-", "", $value['create_time']), 2, 6);
				//号码查看按钮
				$queryData[$key]['phones'] = '<a href="javascript:;" onClick="findPhone(' . $value['id'] . ',' . $create_time . ',\'' . $value['channel_order_id'] . '\')" style="color:#3498db"><u>查看</u></a>';
				//通道点击查看订单附表
				$queryData[$key]['platfrom'] = "<a href='javascript:;' onClick='plat_list({$value['id']},{$create_time})' style='color:#3498db'><u>" . $value['platfrom'] . "</u></a>";
			}
			//状态
			switch ($value['order_code']) {
			case '0':
				$queryData[$key]['order_status'] = '待处理';
				break;
			case '6':
				$queryData[$key]['order_status'] = '失败';
				break;

			default:
				$queryData[$key]['order_status'] = '完成';
				break;
			}
			//计算剩余条数
			// $queryData[$key]['s_balance']   = (int)($value['balance']/$value['price']);

			$queryData[$key]['create_time'] = substr($value['create_time'], 2);
			$queryData[$key]['un_size']     = $value['success_size'] - $value['success_phone_size'] - $value['failure_size'];

			if ($queryData[$key]['un_size'] < 0) {
				$queryData[$key]['un_size'] = 0;
			}
		}

		$list['data'] = $queryData;

		return $list;
	}

	/**
	 * 导出号码数据
	 * @Author    lf
	 * @DateTime  2020-03-19T14:56:22+0800
	 * @copyright [copyright]
	 * @license   [license]
	 * @version   [version]
	 * @param     [type]                   $request [description]
	 * @return    [type]                            [description]
	 */
	public function getOrderNumAll($request) {
		//获取页数、每页行数
		$limit = $request['limit'];
		$page  = $request['page'];
		unset($request['limit']);
		unset($request['page']);

		//时间处理根据时间起始返回每一天的日期[2020-01-12,2020-01-13,...]
		$timeFor = $this->getDateFromRange($request['st'], $request['et']);
		if (count($timeFor) <= 0) {
			return [];
		} elseif (count($timeFor) >= 15) {
			exit('时间范围过大');
		}
		$isp_host = '';
		if ($request['isp_host']) {
			$isp_host = $request['isp_host'];
			unset($request['isp_host']);
		}
		$whereSql = $this->getMysqlWhere($request);

		if ($request['pcode']) {
			$whereSql[] = ['pho.order_phone_code', '=', $request['pcode']];
		}
		if ($isp_host) {
			$whereSql[] = ['pho.isp_host', '=', $isp_host];
		}

		//拼装分表
		$tables = $this->buildPartitionSqlOrderNum('bus_order', $request['field'], $timeFor, $whereSql);

		//查询记录
		$page = ($page - 1) * $limit;
		if ($page < 0) {
			$page = 0;
		}

		//拼装查询
		$qurySql = "SELECT * FROM " . $tables['listSql'] . " LIMIT {$page} , {$limit}";
		//连表
		// $qurySql .= " LEFT JOIN bus_channel cha ON cha.channel_id = sorder.cid ";
		//连表
		// $qurySql .= " LEFT JOIN bus_platfrom pla ON pla.plat_id = sorder.plat_id ";
		//查询
		$queryData = $this->query($qurySql);

		if (strpos($request['field'], 'order_phone_code') !== false || strpos($request['field'], 'order_phone_msg') !== false) {
			//处理结果
			foreach ($queryData as $key => $value) {

				if (isset($value['order_phone_msg'])) {
					if ($queryData[$key]['order_phone_code'] != 6) {
						$queryData[$key]['order_phone_msg'] = '';
					}
				}
				if (isset($value['order_phone_code'])) {
					//状态
					switch ($value['order_phone_code']) {
					case '0':
						$queryData[$key]['order_phone_code'] = '待处理';
						break;
					case '1':
						$queryData[$key]['order_phone_code'] = '已处理';
						break;
					case '5':
						$queryData[$key]['order_phone_code'] = '成功';
						break;
					case '6':
						$queryData[$key]['order_phone_code'] = '失败';
						break;

					default:
						$queryData[$key]['order_phone_code'] = '待处理';
						break;
					}
				}
			}
		}

		return $queryData;
	}

	/**
	 * 构造获取总记录数及主键ID的sql子查询语句 ---------这里查的是总数
	 * @param $table 主表名称
	 * @param $idKey 主键id字段名称
	 * @param string $fields 其它字段名称,多个字段用英文逗号分隔
	 * @param int $num 子表数量
	 * @param string $where 查询条件
	 * @return array
	 */
	public function buildPartitionSqlCount($table, $fields = '', $TimeFor = [], $where = []) {

		$whereStr = '';
		//是否查询号码
		$isSphone = 0;

		//查询的号码
		$phone = '';

		foreach ($where as $key => $value) {
			if ($key > 0) {
				$whereStr .= ' AND';
			}

			$whereStr .= ' ' . $value[0] . ' ' . $value[1] . ' ' . $value[2];

			if ($value[0] == 'pho.phone') {
				$isSphone = 1;

				$phone = $value[2];
			}
		}

		$countTable = [];

		foreach ($TimeFor as $key => $value) {

			$table_name = $table . '_' . $value;

			//查询表
			$countTableone = sprintf('SELECT count(ord.id) num , date(create_time) create_time FROM %s_%s ord where %s', $table, $value, $whereStr);

			if ($isSphone) {
				$countTableone = "SELECT count(ord.id) num , date(ord.create_time) create_time FROM {$table}_{$value} ord LEFT JOIN (SELECT * FROM bus_order_phone_{$value} bopj where bopj.phone = '{$phone}' GROUP BY bopj.order_table_id ) pho on ord.id = pho.order_table_id where {$whereStr}";
			}

			$countTable[] = $countTableone;
		}

		$countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;

		return $countTable;
	}

	/**
	 * 构造获取总记录数及主键ID的sql子查询语句
	 * @param $table 主表名称
	 * @param $idKey 主键id字段名称
	 * @param string $fields 其它字段名称,多个字段用英文逗号分隔
	 * @param int $num 子表数量
	 * @param string $where 查询条件
	 * @return array
	 */
	public function buildPartitionSqlList($table, $fields = '', $TimeFor = [], $where = [], $total = []) {

		$page  = input('page');
		$limit = input('limit');
		//查询记录
		$limitstart = ($page - 1) * $limit;
		if ($limitstart < 0) {
			$limitstart = 0;
		}

		$whereStr = '';
		//是否查询号码
		$isSphone = 0;

		//查询的号码
		$phone = '';

		foreach ($where as $key => $value) {
			if ($key > 0) {
				$whereStr .= ' AND';
			}

			$whereStr .= ' ' . $value[0] . ' ' . $value[1] . ' ' . $value[2];

			if ($value[0] == 'pho.phone') {
				$isSphone = 1;

				$phone = $value[2];
			}
		}

		// $countTable = [];

		$listTable = [];

		$fieldList = [];

		if ($fields) {

			$fieldList = array_merge($fieldList, explode(',', $fields));

			$fieldList = array_unique($fieldList);

		}
		$fieldStr = implode(',', $fieldList);

		foreach ($TimeFor as $key => $value) {

			$table_name = $table . '_' . $value;

			//查询表
			// $countTableone = sprintf('SELECT count(ord.id) num , date(create_time) create_time FROM %s_%s ord where %s', $table, $value, $whereStr);

			//表示第一张表不足$limit行，而第二张表需要LIMIT 从0开始到$limit
			if (count($listTable) > 0) {
				$limitsql = "LIMIT 0 , {$limit}";
			} else {
				//当数据是从第二张以上的表拿的，并且前面有数据，那么前端传过来的$page并非可以直接计算出limitstart，而是要limitstart减去前面的数据
				if ($total[$value]['oldnum'] > 0 && $limitstart >= $total[$value]['oldnum']) {
					$limitstart = $limitstart - $total[$value]['oldnum'];
				}
				$limitsql = "LIMIT {$limitstart} , {$limit}";
			}

			if ($isSphone) {
				// $countTableone = "SELECT count(ord.id) , date(create_time) create_time FROM {$table}_{$value} ord LEFT JOIN (SELECT * FROM bus_order_phone_200429 bopj where bopj.phone = {$phone} GROUP BY bopj.order_table_id ) pho on ord.id = pho.order_table_id where {$whereStr}";

				$listTablesql = "SELECT {$fieldStr} , sms.bus_push_sms , addo.bus_plat_id FROM {$table}_{$value} ord LEFT JOIN bus_order_sms_content_{$value} sms ON sms.bus_order_id = ord.id LEFT JOIN bus_order_phone_{$value} pho ON pho.order_table_id = ord.id LEFT JOIN bus_add_order_{$value} addo ON addo.table_order_id = ord.id WHERE {$whereStr} GROUP BY
                    ord.id,
                    ord.cid,
                    ord.plat_id,
                    ord.isp_host,
                    ord.price,
                    ord.cost,
                    ord.sms_size,
                    ord.success_size,
                    ord.failure_size,
                    ord.success_phone_size,
                    ord.fee,
                    ord.phone_size,
                    ord.port_number,
                    ord.order_id,
                    ord.order_code,
                    ord.create_time,
                    sms.bus_push_sms ORDER BY id DESC " . $limitsql;
			} else {
				$listTablesql = "SELECT {$fieldStr} , sms.bus_push_sms , addo.bus_plat_id FROM {$table}_{$value} ord LEFT JOIN bus_order_sms_content_{$value} sms ON sms.bus_order_id = ord.id LEFT JOIN bus_add_order_{$value} addo ON addo.table_order_id = ord.id WHERE {$whereStr} GROUP BY
                    ord.id,
                    ord.cid,
                    ord.plat_id,
                    ord.isp_host,
                    ord.price,
                    ord.cost,
                    ord.sms_size,
                    ord.success_size,
                    ord.failure_size,
                    ord.success_phone_size,
                    ord.fee,
                    ord.phone_size,
                    ord.port_number,
                    ord.order_id,
                    ord.order_code,
                    ord.create_time,
                    sms.bus_push_sms ORDER BY id DESC " . $limitsql;
			}

			$listTable[] = "SELECT * FROM ({$listTablesql}) t{$key}";
		}

		$listTable = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;

		return $listTable;
	}

	/**
	 * 构造获取总记录数及主键ID的sql子查询语句+++++++号码表
	 * @param $table 主表名称
	 * @param $idKey 主键id字段名称
	 * @param string $fields 其它字段名称,多个字段用英文逗号分隔
	 * @param int $num 子表数量
	 * @param string $where 查询条件
	 * @return array
	 */
	public function buildPartitionSqlOrderNum($table, $fields = '', $TimeFor = [], $where = []) {

		$whereStr = '';
		//拼接条件
		foreach ($where as $key => $value) {
			if ($key > 0) {
				$whereStr .= ' AND';
			}

			$whereStr .= ' ' . $value[0] . ' ' . $value[1] . ' ' . $value[2];
		}

		$countTable = [];

		$listTable = [];

		$fieldList = [];

		if ($fields) {

			$fieldList = array_merge($fieldList, explode(',', $fields));

			$fieldList = array_unique($fieldList);

		}
		$fieldStr = implode(',', $fieldList);

		foreach ($TimeFor as $key => $value) {

			$table_name = $table . '_' . $value;

			//查询表
			$countTable[] = sprintf('SELECT ord.id FROM %s_%s ord where %s', $table, $value, $whereStr);

			$listTable[] = "SELECT {$fieldStr} FROM {$table}_{$value} ord LEFT JOIN bus_order_sms_content_{$value} sms ON sms.bus_order_id = ord.id INNER JOIN bus_order_phone_{$value} pho ON pho.order_table_id = ord.id WHERE {$whereStr}";

		}

		$countTable = '( ' . implode(" UNION ", $countTable) . ') AS ' . $table;

		$listTable = '( ' . implode(" UNION ", $listTable) . ') AS ' . $table;

		$tables = ['countSql' => $countTable, 'listSql' => $listTable];

		return $tables;
	}

	/**
	 * 拼接sql的where部分
	 * @Author    lf
	 * @DateTime  2020-03-19T14:10:49+0800
	 * @copyright [copyright]
	 * @license   [license]
	 * @version   [version]
	 * @return    [type]                   [description]
	 */
	public function getMysqlWhere($request) {

		$relate_id = session('relate_id');

		//条件处理
		// $whereSql = " ord.create_time BETWEEN '{$request['st']}' AND '{$request['et']}' ";
		$whereSql[] = ['ord.create_time', 'BETWEEN', "'{$request['st']}' AND '{$request['et']}'"];
		//通道查询
		if (!empty($request['pla'])) {
			// $whereSql .= " AND ord.plat_id = {$request['pla']} ";
			$whereSql[] = ['ord.plat_id', '=', $request['pla']];
		}
		if (!empty($request['cha'])) {

			// $whereSql .= " AND ord.cid = {$request['cha']} ";
			$whereSql[] = ['ord.cid', '=', $request['cha']];
		}
		if (!empty($request['hm'])) {
			// $whereSql .= " AND pho.phone = {$request['hm']} ";
			$whereSql[] = ['pho.phone', '=', trim($request['hm'])];
		}
		if (!empty($request['sta'])) {
			switch ($request['sta']) {
			case '1':
				// $whereSql .= " AND ord.failure_size = 0 AND ord.success_phone_size > 0 ";
				$whereSql[] = ['ord.failure_size', '=', 0];
				$whereSql[] = ['ord.success_phone_size', '>', 0];
				break;
			case '6':
				// $whereSql .= " AND ord.failure_size > 0 ";
				$whereSql[] = ['ord.failure_size', '>', 0];
				break;
			case '7':
				// $whereSql .= " AND ord.failure_size = 0 AND ord.success_phone_size = 0 ";
				$whereSql[] = ['ord.failure_size', '=', 0];
				$whereSql[] = ['ord.success_phone_size', '=', 0];
				break;

			default:
				# code...
				break;
			}
		}

		//渠道通道用户只能查到自己的订单
		switch (session('user_type')) {
		case 'admin':
			# code...
			break;

		case 'channel':
			$channel_ids   = Db::table('bus_channel')->where('p_id', $relate_id)->column('channel_id');
			$channel_ids[] = $relate_id;

			// $whereSql .= " AND ord.cid in (".implode(",", $channel_ids).") ";
			$whereSql[] = ['ord.cid', 'in', "(" . implode(",", $channel_ids) . ")"];
			break;
		case 'clerk':
			$channel_ids = Db::table('bus_user_and_channel')->where('user_id', session('user_id'))->column('channel_id');

			// $whereSql .= " AND ord.cid in (".implode(",", $channel_ids).") ";
			$whereSql[] = ['ord.cid', 'in', "(" . implode(",", $channel_ids) . ")"];
			break;

		case 'plat':
			// $whereSql .= " AND ord.plat_id = {$relate_id} ";
			$whereSql[] = ['ord.plat_id', '=', $relate_id];
			break;

		default:
			return ['total' => 0, 'data' => 0];
			break;
		}
		if (!empty($request['isp_host'])) {
			$whereSql[] = ['ord.isp_host', '=', $request['isp_host']];
		}

		$this->whereSql = $whereSql;

		return $whereSql;
	}
	/**
	 * 获取指定日期段内每一天的日期和天数
	 * @param  Date  $startdate 开始日期 格式化时间 Y-m-d H:i:s
	 * @param  Date  $enddate   结束日期 格式化时间 Y-m-d H:i:s
	 * @return Array
	 */
	public function getDateFromRange($startdate, $enddate) {

		$startdate = date('Y-m-d 00:00:00', strtotime($startdate));

		$enddate = date('Y-m-d 23:59:59', strtotime($enddate));

		$stimestamp = strtotime($startdate);

		$etimestamp = strtotime($enddate);

		if ($etimestamp < $stimestamp) {
			return [];
		}

		// 计算日期段内有多少天
		$days = (int) ceil(($etimestamp - $stimestamp) / 86400);

		// 保存每天日期
		$date = array();

		for ($i = 0; $i < $days; $i++) {

			$dateOne = date('Y-m-d', $stimestamp + (86400 * $i));

			$date[] = substr(str_replace("-", "", $dateOne), 2);
		}

		foreach ($date as $key => $value) {
			# code...
			//查询表是否存在
			$istable = $this->query("SHOW TABLES LIKE 'bus_order_{$value}'");

			if (!$istable) {
				unset($date[$key]);
			}
		}

		$this->timearr = array_reverse($date);
		return array_reverse($date);
	}

}